package org.lq.shopping.dao.impl;

import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;
import org.lq.shopping.dao.InvoiceDao;
import org.lq.shopping.entity.ShopInvoice;
import org.lq.shopping.test.LogTest;
import org.lq.shopping.util.DruidUtil;
/**
 * 发票的方法实现类
 * @author 闵江沛
 * @date 2018年9月26日
 */
public class InvoiceDaoImpl implements InvoiceDao{
	QueryRunner qr=new QueryRunner(DruidUtil.getDataSource());
	Logger log = Logger.getLogger(InvoiceDaoImpl.class);
	@Override
	public int add(ShopInvoice i) {
		int num = 0;
		String sql = "insert into shop_invoice" + 
				"  (invoice_id, order_id, invoice_type, invoice_name, user_id)" + 
				"values" + 
				"  (invoice_seq.nextval, ?, ?, ?, ?)";
		try {
			num = qr.execute(sql,i.getOrder_id(),i.getInvoice_itype(),i.getInvoice_name(),i.getUser_id() );
		} catch (SQLException e) {
			log.error("这是发票添加的SQL语句异常,SQL语句:"+sql);
		}
		return num;
	}

	@Override
	public int delete(int id) {
		int num=0;
		String sql="delete shop_invoice where invoice_id=?";
		
		try {
			num=qr.execute(sql, id);
		} catch (SQLException e) {
			log.error("这是发票删除的SQL语句异常,SQL语句:"+sql);
		}
		return num;
	}

	@Override
	public int update(ShopInvoice i) {
		int num = 0;
		String sql = "update shop_invoice" + 
				"set" + 
				"   order_id=?, invoice_type=?, invoice_name=?, user_id=?"+
				"where invoice_id=?";
		try {
			num = qr.execute(sql,i.getOrder_id(),i.getInvoice_itype(),i.getInvoice_name(),i.getUser_id(),i.getInvoice_id() );
		} catch (SQLException e) {
			log.error("这是发票添加的SQL语句异常,SQL语句:"+sql);
		}
		return num;
	}

	@Override
	public List<ShopInvoice> findAll() {
		List<ShopInvoice> list = null;
		String sql="select * from shop_invoice order by invoice_id";
		try {
			list =qr.query(sql, new BeanListHandler<>(ShopInvoice.class));
		} catch (SQLException e) {
			log.error("这是发票全部展示的SQL语句异常,SQL语句:"+sql);
		}
		
		return list;
	}

	@Override
	public ShopInvoice findById(int id) {
		ShopInvoice i = null;
		String sql = "select * from shop_invoice where invoice_id = ?";
		try {
			i = qr.query(sql, new BeanHandler<>(ShopInvoice.class),id);
		} catch (SQLException e) {
			log.error("这是发票按ID展示的SQL语句异常,SQL语句:"+sql);
		}
		return i;
	}

	@Override
	public List<ShopInvoice> LikeColumnByValue(String columName, Object value) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getCount() {
		// TODO Auto-generated method stub
		return 0;
	}
	/**
	 * 发票分页展示
	 */
	@Override
	public List<ShopInvoice> findPage(int pageIndex, int pageSize) {
		List<ShopInvoice> list = null;
		String sql = "select * from "
				+ "(select rownum r,r.* from "
				+ "(select * from shop_invoice order by publish_time) r "
				+ "where rownum <= ?) "
				+ "where r> ?";
		int max = pageIndex * pageSize;
		int min =( pageIndex-1 )*pageSize;
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopInvoice.class),max,min);
		} catch (SQLException e) {
			log.error("这是发票全部分页展示的SQL语句异常,SQL语句:"+sql);
		}
		
		return list;
	}

	@Override
	public List<ShopInvoice> findByInvoiceType(String invoicetype) {
		List<ShopInvoice> list = null;
		String sql="select * from shop_invoice where invoice_type=?";
		try {
			list =qr.query(sql, new BeanListHandler<>(ShopInvoice.class),invoicetype);
		} catch (SQLException e) {
			log.error("这是发票按类型展示的SQL语句异常,SQL语句:"+sql);
		}
		
		return list;
	}

	@Override
	public List<ShopInvoice> findPageById(int uid, int pageIndex, int pageSize) {
		List<ShopInvoice> list = null;
		String sql = "select * from "
				+ "(select rownum r,r.* from "
				+ "(select * from shop_invoice where user_id =? order by publish_time) r "
				+ "where rownum <= ?) "
				+ "where r> ?";
		int max = pageIndex * pageSize;
		int min =( pageIndex-1 )*pageSize;
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopInvoice.class),uid,max,min);
		} catch (SQLException e) {
			log.error("这是发票按用户ID分页展示的SQL语句异常,SQL语句:"+sql);
		}
		
		return list;
	}

	@Override
	public List<ShopInvoice> findPageLikeName(String uname, int pageIndex, int pageSize) {
		List<ShopInvoice> list = null;
		String sql = "select * from shop_invoice where user_id in " + 
				"(select user_id from shop_user where uname like '%?%')";
		int max = pageIndex * pageSize;
		int min =( pageIndex-1 )*pageSize;
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopInvoice.class),uname,max,min);
		} catch (SQLException e) {
			log.error("这是根据模糊查询用户姓名分页展示发票的SQL语句异常,SQL语句:"+sql);
		}
		
		return list;
	}

	@Override
	public List<ShopInvoice> findPageByTime(Date startTime, Date endTime, int pageIndex, int pageSize) {
		// TODO Auto-generated method stub
		return null;
	}

}
